package com.xuan.task.Mapper;

import com.xuan.task.domain.HomeworkExampaperView;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface HomeworkExampaperMapper {

    //获取作业下试卷的名称
    @Select(" select t1.homework_id homeworkId,t1.exampaper_id exampaperId,t1.type ,t2.title exampaperName" +
            " from homework_exampaper t1 join exampaper t2 on t1.exampaper_id = t2.exampaper_id" +
            " where homework_id=#{homeworkId}")
    List<HomeworkExampaperView> getHomeworkExampaperByHomeworkId(Integer homeworkId);

    //获取作业下例句、课文和演讲资源包的名称
    @Select(" select t1.homework_id homeworkId,t1.exampaper_id exampaperId,t1.type,t2.resource_bundle_name exampaperName" +
            " from homework_exampaper t1 join resource_bundle t2 on t1.exampaper_id = t2.resource_bundle_id" +
            " where homework_id=#{homeworkId}")
    List<HomeworkExampaperView> getHomeworkResourceBundleByHomeworkId(Integer homeworkId);

    //获取作业下单词资源包的名称
    @Select(" select t1.homework_id homeworkId,t1.exampaper_id exampaperId,t1.type,t2.resource_library_name exampaperName" +
            " from homework_exampaper t1 join vocabulary_resource_library t2 on t1.exampaper_id = t2.resource_library_num" +
            "  where homework_id=#{homeworkId}")
    List<HomeworkExampaperView> getHomeworkVocabularyByHomeworkId(Integer homeworkId);


    //试卷中试题的总数
    @Select(" select count(*)  from exampaper_exam where exampaper_id=#{exampaperId}")
    int getCountByExampaperId(String exampaperId);

    //获取试卷的总分
    @Select("select IFNULL(" +
            " (select sum(score) totalScore  from shuoba_exam where exam_id in (" +
            " select exam_id from exampaper_exam  where exampaper_id=#{exampaperId})),0) totalScore")
    double getExampaperTotalScore(String exampaperId);


    //获取试卷的批次和总得分
    @Select("select batch_id,SUM(score) score  from shuoba_result" +
            " where homework_id=#{homeworkId} and exampaper_id=#{exampaperId} and user_id=#{userId}" +
            " group by batch_id" +
            " order by score desc" +
            " limit 0,1")
    Map<String, Object> getExampaperScoreByHomeworkIdAndUserIdAndExampaperId(@Param("homeworkId") Integer homeworkId, @Param("userId") String userId, @Param("exampaperId") String exampaperId);

    //获取试卷答题的时长 单位秒
    @Select(" select IFNULL(" +
            " (select total_time from user_answer_time_history" +
            " where homework_id =#{homeworkId} and exampaper_id=#{exampaperId} and batch_id =#{batchId} limit 0,1 ),0) totalTime")
    int getAnswerTimeByExampaperIdAndHomeworkId(@Param("homeworkId") Integer homeworkId, @Param("exampaperId") String exampaperId, @Param("batchId") String batchId);


    //试卷中完成的试题个数
    @Select("select count(*)  from (" +
            " select DISTINCT writing_id from shuoba_result" +
            " where homework_id=#{homeworkId} and exampaper_id=#{exampaperId} and batch_id=#{batchId} and user_id=#{userId}) tt")
    int getCompleteCountByExampaperId(@Param("exampaperId") String exampaperId, @Param("userId") String userId, @Param("batchId") String batchId, @Param("homeworkId") Integer homeworkId);


    //获取资源包中资源的总个数
    @Select(" select count(*) from resource_bundle_relation where resource_bundle_id in" +
            "  (select resource_bundle_id from resource_bundle where resource_bundle_id=#{resourceBundleId} )")
    int getCountByResourceBundleId(String resourceBundleId);

    //获取资源包答题的总得分
    @Select("select batch_number,sum(score) score  from (" +
            " select batch_number,resource_id,max(score) score  from resource_practice_records" +
            " where user_id =#{userId} and homework_id=#{homeworkId} and resource_bundle_id=#{resourceBundleId}" +
            " group by resource_id,batch_number) tt" +
            " group by batch_number" +
            " order by score desc limit 0,1")
    Map<String, Object> getResourceBundleScoreByHomeworkIdAndUserIdAndExampaperId(@Param("homeworkId") Integer homeworkId, @Param("userId") String userId, @Param("resourceBundleId") String resourceBundleId);

    //资源包中完成的资源个数
    @Select("select count(*) from (" +
            " select DISTINCT resource_id from resource_practice_records" +
            " where user_id =#{userId} and homework_id=#{homeworkId} and resource_bundle_id=#{resourceBundleId} and batch_number=#{batchId}) tt")
    int getCompleteCountByResourceBundleId(@Param("resourceBundleId") String resourceBundleId, @Param("userId") String userId, @Param("batchId") String batchId, @Param("homeworkId") Integer homeworkId);


    //单词资源包中单词的总数
    @Select("select count(*) from resource_word_relation  where vocabulary_resource_num=#{resourceLibraryNum}")
    int getCountByResourceLibraryNum(@Param("resourceLibraryNum") String resourceLibraryNum);

    //单词资源包中完成的单词个数
    @Select(" select count(*) from" +
            " (select DISTINCT word from word_practice_records" +
            "  where  homework_id=#{homeworkId} and  user_id=#{userId} and unit_name=#{resourceLibraryNum} and batch_number=#{batchId}) tt")
    int getCompleteCountByResourceLibraryNum(@Param("resourceLibraryNum") String resourceLibraryNum, @Param("userId") String userId,
                                             @Param("batchId") String batchId, @Param("homeworkId") Integer homeworkId);

    //获取单词资源包单词的最高的总得分
    @Select(" select batch_number,sum(score) score  from (" +
            "  select batch_number,unit_name,word,max(score) score  from word_practice_records" +
            "  where  user_id=#{userId} and homework_id=#{homeworkId} and unit_name=#{resourceLibraryNum}" +
            "  group by batch_number,unit_name,word) tt" +
            "  group by batch_number order by score desc limit 0,1")
    Map<String, Object> getVocabularyScoreByHomeworkIdAndUserIdAndResourceLibraryNum(@Param("homeworkId") Integer homeworkId, @Param("userId") String userId,
                                                                                     @Param("resourceLibraryNum") String resourceLibraryNum);


}
